import pymysql
import time
from concurrent.futures import ThreadPoolExecutor, as_completed
import logging
import traceback
from datetime import datetime

# 日志文件配置
log_file = f"migration_errors_{datetime.now().strftime('%Y%m%d_%H%M%S')}.log"
logging.basicConfig(
    filename=log_file,
    level=logging.ERROR,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# 数据库连接配置
source_db_config = {
    "host": "127.0.0.1",  # ECShop 数据库地址
    "user": "root",
    "password": "123456",
    "database": "maigeyin",
}

target_db_config = {
    "host": "127.0.0.1",  # FastAdmin 数据库地址
    "user": "root",
    "password": "123456",
    "database": "5i40",
}



def get_source_connection():
    """获取源数据库连接"""
    return pymysql.connect(**source_db_config)


def get_target_connection():
    """获取目标数据库连接"""
    return pymysql.connect(**target_db_config)


def clear_target_tables():
    """清空目标数据库相关表数据"""
    print("清空目标数据库相关表数据...")
    target_conn = get_target_connection()
    target_cursor = target_conn.cursor()
    try:
        tables = ["fa_shop_category", "fa_shop_goods", "fa_shop_goods_sku", 
                 "fa_shop_goods_sku_spec", "fa_shop_spec", "fa_shop_spec_value"]
        for table in tables:
            sql = f"TRUNCATE TABLE {table}"
            target_cursor.execute(sql)
            print(f"已清空表 {table}")
        target_conn.commit()
    except Exception as e:
        logging.error(f"清空目标表时发生错误: {e}")
        print(f"清空目标表时发生错误: {e}")
    finally:
        target_cursor.close()
        target_conn.close()


def create_default_category():
    """创建默认分类"""
    print("创建默认分类...")
    target_conn = get_target_connection()
    target_cursor = target_conn.cursor()
    try:
        sql = """
            INSERT INTO fa_shop_category 
            (id, name, pid, description, isnav, weigh, status, createtime, updatetime)
            VALUES (999, '其他杂志', 0, '未匹配分类', 1, 0, 'normal', %s, %s)
        """
        current_time = int(time.time())
        target_cursor.execute(sql, (current_time, current_time))
        target_conn.commit()
        print("默认分类创建成功！")
    except Exception as e:
        logging.error(f"创建默认分类时发生错误: {e}")
        print(f"创建默认分类时发生错误: {e}")
    finally:
        target_cursor.close()
        target_conn.close()


def add_slash_to_image_path(path):
    """为商品图片路径前加 '/'"""
    if path and not path.startswith("/"):
        return f"/{path}"
    return path or ""

def process_single_sku(product, new_goods_id, base_price, sku_map, target_db_config):
    """处理单个 SKU 的函数"""
    try:
        # 创建新的数据库连接（每个线程独立的连接）
        target_conn = pymysql.connect(**target_db_config)
        target_cursor = target_conn.cursor(pymysql.cursors.DictCursor)

        goods_sn = product['product_sn']
        product_number = product['product_number']
        goods_attr = product['goods_attr']

        # SKU ID 处理
        if not goods_attr:
            sku_id = "0"
        else:
            try:
                sku_attrs = [attr.strip() for attr in goods_attr.split("|") if attr.strip()]
                sku_ids = []
                for attr in sku_attrs:
                    try:
                        attr_id = int(attr)
                        if attr_id in sku_map:
                            sku_ids.append(str(sku_map[attr_id]))
                    except (ValueError, TypeError):
                        continue
                sku_id = ",".join(sku_ids) if sku_ids else "0"
            except Exception:
                sku_id = "0"

        # 价格计算规则优化
        years = 1  # 默认1年
        if goods_attr:
            # 使用更精确的年份匹配
            if any(year in goods_attr.lower() for year in ['两年', '2年', 'two']):
                years = 2
            elif any(year in goods_attr.lower() for year in ['三年', '3年', 'three']):
                years = 3

        # 计算最终价格
        final_price = float(base_price) * years

        # 插入 SKU 数据
        sku_sql = """
            INSERT INTO fa_shop_goods_sku 
            (goods_id, goods_sn, sku_id, price, stocks, createtime, updatetime)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        current_time = int(time.time())
        sku_params = (new_goods_id, goods_sn, sku_id, final_price, 
                     product_number, current_time, current_time)
        target_cursor.execute(sku_sql, sku_params)
        target_conn.commit()

    except Exception as e:
        logging.error(f"处理 SKU 时发生错误: {str(e)}, goods_sn: {goods_sn}")
        print(f"处理 SKU 时发生错误: {str(e)}, goods_sn: {goods_sn}")
    finally:
        if 'target_cursor' in locals():
            target_cursor.close()
        if 'target_conn' in locals():
            target_conn.close()

def migrate_sku(old_goods_id, new_goods_id, source_cursor, target_cursor, target_conn):
    """处理规格和 SKU 数据"""
    try:
        # 获取商品基础价格
        source_cursor.execute("SELECT shop_price FROM ecs_goods WHERE goods_id = %s", (old_goods_id,))
        base_price = source_cursor.fetchone()['shop_price']

        # 获取商品的规格属性
        source_cursor.execute("SELECT * FROM ecs_goods_attr WHERE goods_id = %s", (old_goods_id,))
        goods_attrs = source_cursor.fetchall()

        spec_map = {}  # 记录规格模板
        sku_map = {}  # 记录 SKU 数据

        # 处理规格模板
        for attr in goods_attrs:
            attr_id = attr['attr_id']
            attr_value = attr['attr_value']

            # 确保规格模板存在
            if attr_id not in spec_map:
                spec_sql = """
                    INSERT INTO fa_shop_spec 
                    (name, createtime, updatetime) 
                    VALUES (%s, %s, %s)
                """
                current_time = int(time.time())
                spec_params = (f"规格{attr_id}", current_time, current_time)
                target_cursor.execute(spec_sql, spec_params)
                target_conn.commit()

                target_cursor.execute("SELECT LAST_INSERT_ID() AS id")
                spec_map[attr_id] = target_cursor.fetchone()['id']

            # 插入规格值
            spec_value_sql = """
                INSERT INTO fa_shop_spec_value 
                (spec_id, value, createtime, updatetime) 
                VALUES (%s, %s, %s, %s)
            """
            current_time = int(time.time())
            spec_value_params = (spec_map[attr_id], attr_value, current_time, current_time)
            target_cursor.execute(spec_value_sql, spec_value_params)
            target_conn.commit()

            target_cursor.execute("SELECT LAST_INSERT_ID() AS id")
            spec_value_id = target_cursor.fetchone()['id']

            # 插入 fa_shop_goods_sku_spec 表
            sku_spec_sql = """
                INSERT INTO fa_shop_goods_sku_spec 
                (goods_id, spec_id, spec_value_id, createtime, updatetime) 
                VALUES (%s, %s, %s, %s, %s)
            """
            current_time = int(time.time())
            sku_spec_params = (new_goods_id, spec_map[attr_id], spec_value_id, current_time, current_time)
            target_cursor.execute(sku_spec_sql, sku_spec_params)
            target_conn.commit()

            sku_map[attr['goods_attr_id']] = spec_value_id

        # 处理 SKU - 使用多线程
        source_cursor.execute("SELECT * FROM ecs_products WHERE goods_id = %s", (old_goods_id,))
        products = source_cursor.fetchall()

        # 创建线程池
        with ThreadPoolExecutor(max_workers=10) as executor:
            # 提交所有 SKU 处理任务
            futures = [
                executor.submit(
                    process_single_sku,
                    product,
                    new_goods_id,
                    base_price,
                    sku_map,
                    target_db_config
                )
                for product in products
            ]

            # 等待所有任务完成
            for future in as_completed(futures):
                try:
                    future.result()
                except Exception as e:
                    logging.error(f"SKU 处理任务失败: {str(e)}")
                    print(f"SKU 处理任务失败: {str(e)}")

    except Exception as e:
        error_msg = f"迁移商品 ID {old_goods_id} 的 SKU 时发生错误: {str(e)}"
        logging.error(error_msg)
        print(error_msg)


def migrate_goods(category_map):
    """迁移商品数据，并处理图片路径和 SKU 数据"""
    source_conn = get_source_connection()
    target_conn = get_target_connection()
    source_cursor = source_conn.cursor(pymysql.cursors.DictCursor)
    target_cursor = target_conn.cursor(pymysql.cursors.DictCursor)

    try:
        # 获取商品数据
        source_cursor.execute("SELECT * FROM ecs_goods")
        goods_list = source_cursor.fetchall()
        total_goods = len(goods_list)
        print(f"开始迁移 {total_goods} 个商品...")

        for index, goods in enumerate(goods_list, 1):
            try:
                goods_id = goods['goods_id']
                cat_id = goods['cat_id']
                category_id = category_map.get(cat_id, 999)

                # 处理图片路径
                goods_thumb = add_slash_to_image_path(goods['goods_thumb'])
                goods_img = add_slash_to_image_path(goods['goods_img'])
                original_img = add_slash_to_image_path(goods['original_img'])

                # 替换描述中的特定文本
                description = goods['goods_brief']
                if description:
                    description = description.replace('杂志E家', '麦格音').replace('杂志2007', '麦格音')

                # 插入商品数据
                goods_sql = """
                    INSERT INTO fa_shop_goods 
                    (category_id, goods_sn, title, description, price, stocks, image, 
                     images, spectype, createtime, updatetime)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                current_time = int(time.time())
                goods_params = (
                    category_id, goods['goods_sn'], goods['goods_name'], description,
                    goods['shop_price'], goods['goods_number'], goods_thumb,
                    f"{goods_img},{original_img}", 1, current_time, current_time
                )
                target_cursor.execute(goods_sql, goods_params)
                target_conn.commit()

                # 获取新商品 ID
                target_cursor.execute("SELECT LAST_INSERT_ID() AS id")
                new_goods_id = target_cursor.fetchone()['id']

                # 处理规格和 SKU
                migrate_sku(goods_id, new_goods_id, source_cursor, target_cursor, target_conn)

                print(f"已迁移商品 {index}/{total_goods}: {goods['goods_name']}")

            except Exception as e:
                error_msg = f"迁移商品 ID {goods_id} ({goods['goods_name']}) 时发生错误: {str(e)}"
                logging.error(error_msg)
                print(error_msg)
                continue

    except Exception as e:
        logging.error(f"迁移商品过程中发生错误: {str(e)}")
        print(f"迁移商品过程中发生错误: {str(e)}")
    finally:
        source_cursor.close()
        target_cursor.close()
        source_conn.close()
        target_conn.close()


def main():
    """主函数"""
    try:
        print("开始数据迁移...")
        
        # 清空目标表
        clear_target_tables()
        
        # 创建默认分类
        create_default_category()
        
        # 示例分类映射
        category_map = {0: 999}
        
        # 迁移商品
        migrate_goods(category_map)
        
        print("数据迁移完成！")
        
    except Exception as e:
        logging.error(f"迁移过程中发生错误: {str(e)}\n{traceback.format_exc()}")
        print(f"迁移过程中发生错误: {str(e)}")


if __name__ == "__main__":
    main()
